USE <database_name>;
GO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- make sure the name passed is appropriately quoted 
DECLARE @index_name AS SYSNAME = QUOTENAME('<index_name>');

-- dig into the plan cache and find all plans using this index 
;WITH XMLNAMESPACES 
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')    
SELECT 
	stmt.value('(@StatementText)[1]', 'varchar(max)') AS sql_text, 
	obj.value('(@Database)[1]', 'varchar(128)') AS database_name, 
	obj.value('(@Schema)[1]', 'varchar(128)') AS 'schema_name', 
	obj.value('(@Table)[1]', 'varchar(128)') AS table_name, 
	obj.value('(@Index)[1]', 'varchar(128)') AS index_name, 
	obj.value('(@IndexKind)[1]', 'varchar(128)') AS index_kind, 
	cp.plan_handle, 
	query_plan 
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt) 
CROSS APPLY stmt.nodes('.//IndexScan/Object[@Index=sql:variable("@index_name")]') AS idx(obj) 
OPTION(MAXDOP 1, RECOMPILE); 